package co.th.bjc.md.helper.impl;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import co.th.bjc.md.dao.NewLetters;
import co.th.bjc.md.helper.entries.HelperDatabse;

public class NewLettersHelper extends HelperDatabse {

	
	public List<NewLetters> getAllNewLetters(){
		Connection con = null;
		PreparedStatement pstmt = null;
		List <NewLetters> newLettersList = new ArrayList<NewLetters>();
		
		try{
			con = getConnection();
			String sql = "SELECT * FROM newletters";
			System.out.println(sql);
			pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				NewLetters newLetters = new NewLetters();
				newLetters.setLettersID(rs.getBigDecimal(1));
				newLetters.setLetterHead(rs.getString(2));
				newLetters.setDesciption(rs.getString(3));
				newLetters.setDesciption2(rs.getString(4));
				
				InputStream bl = rs.getBinaryStream(5);
//		        byte[] pict = bl.getBytes(1,(int)bl.length());
//		        InputStream is = new ByteArrayInputStream(pict);
//		        newLetters.setImage(bl);
				 
			}
			pstmt.close();
			con.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return newLettersList;
		
	}
	public List<NewLetters> getTopNewLetters(){
		Connection con = null;
		PreparedStatement pstmt = null;
		List <NewLetters> newLettersList = new ArrayList<NewLetters>();
		
		try{
			con = getConnection();
			String sql = "SELECT * FROM newletters a ORDER BY a.letters_id DESC LIMIT 2 ";
			System.out.println(sql);
			pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				NewLetters newLetters = new NewLetters();
				newLetters.setLettersID(rs.getBigDecimal(1));
				newLetters.setLetterHead(rs.getString(2));
				newLetters.setDesciption(rs.getString(3));
				newLetters.setDesciption2(rs.getString(4));
				
//				InputStream bl = rs.getBinaryStream(5);
//		        byte[] pict = bl.getBytes(1,(int)bl.length());
//		        InputStream is = new ByteArrayInputStream(pict);
//		        newLetters.setImage(bl);
		        
		        newLettersList.add(newLetters);
				 
			}
			pstmt.close();
			con.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return newLettersList;
		
	}
	public InputStream getImageNewLetters(BigDecimal newLetters_id) {
		// TODO Auto-generated method stub
		
		Connection con = null;
		PreparedStatement pstmt = null;
		InputStream newLettersImage = null;
		try{
			con = getConnection();
			String sql = "SELECT n.image FROM newletters n WHERE n.letters_id = "+newLetters_id.intValue();
			System.out.println(sql);
			pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				newLettersImage = rs.getBinaryStream(1);
			}
			pstmt.close();
			con.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return newLettersImage;
	}
}
